From d6445356fa095b71e35bbf88d8abb781e6dbcea0 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Thu, 23 Jul 2026 23:48:23 +0000 Subject: [PATCH] Create the BREC_NOTES_VIEW --- db/schemas/sokwedb/views/Makefile | 3 +- .../sokwedb/views/create/brec_notes_view.m4 | 69 + doc/diagrams/brec_notes_view.svg | 1281 +++++++++++++++++ doc/src/epilog.inc.m4 | 1 + doc/src/views.m4 | 18 +- doc/src/views/brec_notes_view.m4 | 236 +++ 6 files changed, 1601 insertions(+), 7 deletions(-) create mode 100644 db/schemas/sokwedb/views/create/brec_notes_view.m4 create mode 100644 doc/diagrams/brec_notes_view.svg create mode 100644 doc/src/views/brec_notes_view.m4 diff --git a/db/schemas/sokwedb/views/Makefile b/db/schemas/sokwedb/views/Makefile index a3856ed..2d817be 100644 --- a/db/schemas/sokwedb/views/Makefile +++ b/db/schemas/sokwedb/views/Makefile @@ -23,7 +23,8 @@ ORDER := biography \ obs \ dyads \ - pantgrunts_view + pantgrunts_view \ + brec_notes_view ## ## CAUTION: This Makefile is not designed to be run directly. It is normally diff --git a/db/schemas/sokwedb/views/create/brec_notes_view.m4 b/db/schemas/sokwedb/views/create/brec_notes_view.m4 new file mode 100644 index 0000000..bd947b3 --- /dev/null +++ b/db/schemas/sokwedb/views/create/brec_notes_view.m4 @@ -0,0 +1,69 @@ +dnl Copyright (C) 2026 The Meme Factory, Inc., http://www.karlpinc.com/ +dnl +dnl This program is free software: you can redistribute it and/or modify +dnl it under the terms of the GNU Affero General Public License as published +dnl by the Free Software Foundation, either version 3 of the License, or +dnl (at your option) any later version. +dnl +dnl This program is distributed in the hope that it will be useful, +dnl but WITHOUT ANY WARRANTY; without even the implied warranty of +dnl MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +dnl GNU Affero General Public License for more details. +dnl +dnl You should have received a copy of the GNU Affero General Public License +dnl along with this program. If not, see . +dnl +dnl Karl O. Pinc +dnl +dnl +dnl m4 includes +include(`copyright.m4')dnl +include(`constants.m4')dnl +include(`tablemacros.m4')dnl +include(`grants.m4')dnl +dnl + +CREATE OR REPLACE VIEW brec_notes_view ( + wid + ,date + ,animid + ,type + ,commid + ,eid + ,time + ,observation + ,comments + ,observer + ,translator + ,transcribedby + ,voc + ,vocid + ,groomingaggression + ,duplicate + ,notes + ,event_notes + ) + AS + SELECT obs.wid + , obs.date + , obs.animid + , obs.type + , obs.commid + , obs.eid + , obs.start AS time + , brecord_notes.observation + , brecord_notes.comments + , brecord_notes.observer + , brecord_notes.translator + , brecord_notes.transcribedby + , brecord_notes.voc + , brecord_notes.vocid + , brecord_notes.groomingaggression + , brecord_notes.duplicate + , obs.notes + , obs.event_notes + FROM obs + JOIN brecord_notes ON (brecord_notes.eid = obs.eid); + + +grant_priv(`BREC_NOTES_VIEW') diff --git a/doc/diagrams/brec_notes_view.svg b/doc/diagrams/brec_notes_view.svg new file mode 100644 index 0000000..a296431 --- /dev/null +++ b/doc/diagrams/brec_notes_view.svg @@ -0,0 +1,1281 @@ + +image/svg+xmlDetails of theB-Recordnote's followThe B-RecordnoteOBSWIDCommID *TypeDateAnimIDEIDTimeNotesEvent_NotesBRECORD_NOTESObservationCommentsObserverTranslatorTranscribedByVocVocIDGroomingAggressionDuplicateEID diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index bcec4ad..f61652b 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -909,6 +909,7 @@ elo_ranks_daily_rst(sdb_male, MT)dnl .. View name substitutions. .. |BIOGRAPHY| replace:: :ref:`BIOGRAPHY ` +.. |BREC_NOTES_VIEW| replace:: :ref:`BREC_NOTES_VIEW ` .. |DYADS| replace:: :ref:`DYADS ` .. |OBS| replace:: :ref:`OBS ` .. |PANTGRUNTS_VIEW| replace:: :ref:`PANTGRUNTS_VIEW ` diff --git a/doc/src/views.m4 b/doc/src/views.m4 index 7cd6fce..d119b1c 100644 --- a/doc/src/views.m4 +++ b/doc/src/views.m4 @@ -31,17 +31,23 @@ in the old MS Access database. Others reproduce common query patterns, eliminiating the need to connect (join) multiple tables. -+-------------+----------------------+-----------------+-------------------+ -| View | One row for each | Purpose | Tables/Views used | -+=============+======================+=================+===================+ -| |BIOGRAPHY| | |BIOGRAPHY_DATA| row | Reproduce "old" | |BIOGRAPHY_DATA| | -| | | data | | -+-------------+----------------------+-----------------+-------------------+ ++-------------------+----------------------+----------------------+-------------------+ +| View | One row for each | Purpose | Tables/Views used | ++===================+======================+======================+===================+ +| |BIOGRAPHY| | |BIOGRAPHY_DATA| row | Reproduce "old" data | |BIOGRAPHY_DATA| | +| | | data | | ++-------------------+----------------------+----------------------+-------------------+ +| |BREC_NOTES_VIEW| | |BRECORD_NOTES| row | Simplify querys that | |OBS|, | +| | | join |BRECORD_NOTES| | |BRECORD_NOTES| | +| | | with other |EVENTS| | | +| | | related tables | | ++-------------------+----------------------+----------------------+-------------------+ .. toctree:: :maxdepth: 3 views/biography.rst + views/brec_notes_view.rst .. _data_modification_views: diff --git a/doc/src/views/brec_notes_view.m4 b/doc/src/views/brec_notes_view.m4 new file mode 100644 index 0000000..5fd88cb --- /dev/null +++ b/doc/src/views/brec_notes_view.m4 @@ -0,0 +1,236 @@ +.. Copyright (C) 2026 The Meme Factory, Inc. www.karlpinc.com + + This program is free software: you can redistribute it and/or modify + it under the terms of the GNU Affero General Public License as + published by the Free Software Foundation, either version 3 of the + License, or (at your option) any later version. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU Affero General Public License for more details. + + You should have received a copy of the GNU Affero General Public License + along with this program. If not, see . + +.. M4 setup +include(constants.m4)dnl +include(macros.m4)dnl +sdb_rst_quotes(`on')dnl +sdb_generated_rst()dnl + +.. _BREC_NOTES_VIEW: + +BREC_NOTES_VIEW (BRECord NOTES VIEW) +------------------------------------ + +.. |BREC_NOTES_VIEW_summary| replace:: + This view contains one row for each row on the |BRECORD_NOTES| + table. + The view simplifies writing queries which show |BRECORD_NOTES| + together with the content of other |EVENTS| related tables, for the + purpose of comparing each with the other. + +|BREC_NOTES_VIEW_summary| +The rows of the BREC_NOTES_VIEW are |BRECORD_NOTES| rows, but extended +with the time, date, |BIOGRAPHY_DATA.AnimID|, and other information +found on |EVENTS| and |WATCHES|. + +The BREC_NOTES_VIEW view is convenient when writing queries.\ [#f1]_ + +Below is a query that shows some |BRECORD_NOTES| information alongside +pantgrunt information, when a note was taken during the same follow, at +exactly the same time, as the pantgrunt occurred. + +.. code-block:: sql + :caption: B-Record observations related to pantgrunt data + + SELECT brec_notes_view.wid, brec_notes_view.eid + , brec_notes_view.date + , brec_notes_view.animid, brec_notes_view.time + , brec_notes_view.commid, brec_notes_view.observation + , pantgrunts_view.actor, pantgrunts_view.recipient + , pantgrunts_view.style, pantgrunts_view.commid + , pantgrunts_view.source + FROM brec_notes_view + JOIN pantgrunts_view + ON (pantgrunts_view.date = brec_notes_view.date + AND pantgrunts_view.animid = brec_notes_view.animid + AND pantgrunts_view.time = brec_notes_view.time) + ORDER BY brec_notes_view.animid, brec_notes_view.date + , brec_notes_view.time, brec_notes_view.eid; + +Here is a second query that shows some |BRECORD_NOTES| information +alongside grooming information, when a note was taken during the same +follow as the grooming was recorded, while the grooming was ongoing. + +.. code-block:: sql + :caption: B-Record observations related to grooming data + + SELECT brec_notes_view.wid, brec_notes_view.eid + , brec_notes_view.date + , brec_notes_view.animid, brec_notes_view.time + , brec_notes_view.commid, brec_notes_view.observation + , dyads.start, dyads.stop + , dyads.actor, dyads.recipient + , dyads.style, dyads.commid + , groomings.initiator, groomings.terminator + FROM brec_notes_view + JOIN dyads + ON (dyads.date = brec_notes_view.date + AND dyads.animid = brec_notes_view.animid) + JOIN groomings + WHERE dyads.start <= brec_notes_view.time + AND brec_notes_view.time <= dyads.stop + AND dyads.behavior = 'sdb_grooming' + ORDER BY brec_notes_view.animid, brec_notes_view.date + , brec_notes_view.time, brec_notes_view.eid; + +There is, at the time of this writing, no view incorporating +|GROOMINGS|, to make it eaiser to query. +So the above query explicitly uses |DYADS| and |GROOMINGS|. + +And, here is a query showing some |BRECORD_NOTES| information alongside +arrival information, when a note was taken during the same follow, +within 5 minutes of the time the arriving chimpanzee arrived. + +.. code-block:: sql + :caption: B-Record observations related to arrival data + + SELECT brec_notes_view.wid, brec_notes_view.eid, brec_notes_view.date + , brec_notes_view.animid, brec_notes_view.time + , brec_notes_view.commid, brec_notes_view.observation + , roles.participant AS whoarrived + , obs.start AS arrivaltime + , obs.stop AS departuretime + , obs.commid + , arrivals.seq, arrivals.neststart, arrivals.nestend + , arrivals.cycle, arrivals.datasource + FROM brec_notes_view + JOIN obs + ON (obs.date = brec_notes_view.date + AND obs.animid = brec_notes_view.animid) + JOIN roles + ON (roles.eid = obs.eid) + JOIN arrivals + ON (arrivals.eid = obs.eid) + WHERE obs.start - '5 minutes'::INTERVAL <= brec_notes_view.time + AND brec_notes_view.time <= obs.start + '5 minutes'::INTERVAL + AND obs.behavior = 'sdb_arrival' + ORDER BY brec_notes_view.animid, brec_notes_view.date + , brec_notes_view.time, brec_notes_view.eid + , obs.start, obs.eid; + +There is, at the time of this writing, no view using |ARRIVALS| to +make it easy to query. +So the above query explicitly uses |OBS| and |ARRIVALS|, instead of +|DYADS| as in the grooming query above, because arrivals involve a +single individual instead of two individuals. +Further, |OBS| does not contain any data from |ROLES| (because that +varies depending on the event), so |ROLES| must be added to the query +to show the |BIOGRAPHY_DATA|.|BIOGRAPHY_DATA.AnimID| of the individual +who arrived. + +.. _BREC_NOTES_VIEW_Definition: + +Definition +`````````` +.. include:: /view_sql/sokwedb/brec_notes_view.sql + :code: sql + + +.. _BREC_NOTES_VIEW_ER_Diagram: + +ER Diagram +`````````` + +.. figure:: sdb_er_image_path(images/brec_notes_view) + :alt: BREC_NOTES_VIEW Entity-Relationship Diagram + :width: 100 % + + BREC_NOTES_VIEW + + +.. _BREC_NOTES_VIEW_Columns: + +Columns of the BREC_NOTES_VIEW View +``````````````````````````````````` + +.. Configure the From column to be 35% of the line length, Description 40% + Otherwise, the column names can be hyphenated and contain line breaks. +.. tabularcolumns:: l \Y{.35} \Y{.40} + +.. table:: The columns of the BREC_NOTES_VIEW view + :widths: auto + :class: longtable + + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Column | From | Description | + +====================+======================================================+=====================================================================+ + | WID | |OBS|.WID | Identifier of the related |WATCHES| row | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Date | |OBS|.Date | Date of the event | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | AnimID | |OBS|.AnimID | Focal of follow, or focal of a non-existent follow, or an | + | | | un-interesting AnimID | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Type | |OBS|.Type | Category of observation, often determining the data collection | + | | | protocol: follow, feeding station attendance, groom scans, etc. | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | CommID | |OBS|.CommID | The community identifier associated with the Date/AnimID/Type | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | EID | |OBS|.EID | Identifier of the |EVENTS| row | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Behavior | |OBS|.Behavior | Code designating the type of event observed | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Time | |OBS|.Start | Time the B-Record note was recorded | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Observation | |BRECORD_NOTES|.\ |BRECORD_NOTES.Observation| | A narrative account of the observation | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Comments | |BRECORD_NOTES|.\ |BRECORD_NOTES.Comments| | Additional comments | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Observer | |BRECORD_NOTES|.\ |BRECORD_NOTES.Observer| | Name of field assistant who recorded the notes | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Translator | |BRECORD_NOTES|.\ |BRECORD_NOTES.Translator| | Name of translator | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | TranscribedBy | |BRECORD_NOTES|.\ |BRECORD_NOTES.TranscribedBy| | Name of person who transcribed the translation | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Voc | |BRECORD_NOTES|.\ |BRECORD_NOTES.Voc| | Text to do with chimpanzee vocalizations | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | VocID | |BRECORD_NOTES|.\ |BRECORD_NOTES.VocID| | More text to do with chimpanzee vocalization | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | GroomingAggression | |BRECORD_NOTES|.\ |BRECORD_NOTES.GroomingAggression| | The English translation of the text of the observation | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Duplicate | |BRECORD_NOTES|.\ |BRECORD_NOTES.Duplicate| | Text related to duplicate data tracking | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Notes | |OBS|.Notes | Textual notes on the observation for the Date/AnimID/Type | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + | Event_Notes | |OBS|.Event_Notes | Textual notes on the event | + +--------------------+------------------------------------------------------+---------------------------------------------------------------------+ + + +.. _BREC_NOTES_VIEW_Operations_Allowed: + +Operations Allowed +`````````````````` + +None. + +.. rubric:: Footnotes + +.. [#f1] + This is particularly true because, in order to join |BRECORD_NOTES| + with other |EVENTS|\ -based tables, the join is done on Date, Time, + and AnimID, all columns found on the |EVENTS| and |WATCHES| tables. + (Or found via the |OBS| or |DYADS| views.) + But in order to join on, say, Date, the |WATCHES| row related to + the |BRECORD_NOTES| row must be part of the query, as must the + |WATCHES| row related to the behavioral event of interest. + The |WATCHES| row must appear twice in the same query. + Doing this requires aliasing the name of the |WATCHES| table using + the ``AS`` syntax, a somewhat advanced technique. + + When the BREC_NOTES_VIEW is used, the columns of the |WATCHES| and + |EVENTS| (and |OBS|) tables are already "aliased" under the + "BREC_NOTES_VIEW" name, simplifying query construction whenever any + of these relations must also be written into the query. -- 2.34.1